﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;

namespace Utility
{
    /// <summary>
    /// ReadExcelHepler 的摘要说明
    /// </summary>
    public class ExcelHepler
    {
        /// <summary>
        /// 从Excel导入到DataSet
        /// </summary>
        /// <param name="ExcelPath">文件的物理路径</param>
        /// <param name="SheetName">Excel表名</param>
        /// <returns></returns>
        public static DataSet ExcelToDataSet(string ExcelPath, string SheetName)
        {
            DataSet mDataSet = new DataSet();
            try
            {
                //Microsoft Excel 8.0 (97)、9.0 (2000) 和 10.0 (2002) 工作簿，请使用 Excel 8.0。 
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ExcelPath + ";" + "Extended Properties=Excel 8.0;";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                string strExcel = "";
                OleDbDataAdapter myCommand = null;
                strExcel += "select * from [" + SheetName + "$];";

                myCommand = new OleDbDataAdapter(strExcel, strConn);

                myCommand.Fill(mDataSet);
            }
            catch (Exception ex)
            {
                //System.Windows.Forms.MessageBox.Show(ex.Message);
            }
            return mDataSet;
        }

        /// <summary>
        /// 从DataTable导出到Excel 每张表不同，里面的数据不同，所以要单独针对每张表写
        /// </summary>
        /// <param name="FileName"></param>
        public static void OutputExcel(string FileName, DataTable dt)
        {
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Charset = "UTF-8";
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;FileName=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.Default));

            System.Text.StringBuilder sb = new System.Text.StringBuilder();

            sb.Append("<table style='boder:1px;'><tr>用户编号</td><td>用户角色</td><td>用户名</td><td>密码</td><td>性别</td><td>Email</td><td>电话</td><td>地址</td><td>钱数</td><td>身份证号</td><td>用户状态</td></tr>");
            foreach (DataRow dr in dt.Rows)
            {
                //sb.Append("<tr><td>" + dr[0].ToString() + "</td><td>" + dr[1] + "</td><td>" + dr[2] + "</td><td>" +dr[3]) + "</td><td>" + dr[4] + "</td><td>" + dr[5]+ "</td><td>" + dr[6] + "</td><td>"+dr[7]+ "</td><td>" + dr[8] + "</td><td>" +dr[9]+ "</td><td>" + dr[10] + "</td></tr>";);
            }
            sb.Append("</table>");
            HttpContext.Current.Response.Write(sb.ToString());
            HttpContext.Current.Response.End();
        }
    }
}